package com.jh.user.repository;

import com.jh.user.pojo.Area;
import com.jh.user.pojo.BankBranch;
import com.jh.user.pojo.City;
import com.jh.user.pojo.Province;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface BankBranchRepository extends JpaRepository<BankBranch,String>,JpaSpecificationExecutor<BankBranch>{
	@Query(value="select b.bankName from BankBranch b where b.provinceId like %:province% and b.city like %:city% and b.topName like %:topName%")
	List<String> querybranchinfo(@Param("province") String province, @Param("city") String city, @Param("topName") String topName);

	@Query("select province from Province province")
	List<Province> findAllProvince();

	@Query("select city from City city where city.provinceid=:provinceid")
	List<City> findAllCity(@Param("provinceid") String provinceid);

	@Query("select city from City city where city.cityid=:cityid")
	City findByCityid(@Param("cityid") String cityid);

	@Query("select province from Province province where province.provinceid=:provinceid")
	Province findByProvinceId(@Param("provinceid") String provinceid);

	@Query("select city from City city ")
	List<City> findAllCity();

	@Query("select area from Area area where area.cityid=:cityid")
	List<Area> findAllArea(@Param("cityid") String cityid);

	@Query("select bb.bankNo from BankBranch bb where bb.bankName = :bankName")
	public BankBranch getBankCodeByBankName(@Param("bankName") String bankName);

	@Query("select bb from BankBranch bb where bb.bankNo in :bankNoList")
	List<BankBranch> findBankBranch(@Param("bankNoList") List<String> bankNoList);

	@Query(value = "select b from BankBranch b where b.provinceId = :provinceId and b.cityId = :cityId and b.topName like %:topName%")
    List<BankBranch> findBankNameList(@Param("provinceId") String provinceId, @Param("cityId") String cityId, @Param("topName") String topName);

	@Query(value="select b.bankName from BankBranch b where b.provinceId like %:provinceId% and b.cityId like %:cityId% and b.topName like %:topName%")
	List<String> findAllBranch3(@Param("provinceId") String provinceId, @Param("cityId") String cityId, @Param("topName") String topName);
}